<?php

/**
  *
  * Database method wrapper class for PDO
  * PHP versions 4 and 5
  *
  * This program is free software; you can redistribute it and/or
  * modify it under the terms of the GNU General Public License
  * as published by the Free Software Foundation; either version 2
  * of the License, or (at your option) any later version.
  * (see nucleus/documentation/index.html#license for more info)
  *
  * @author    shizuki
  * @copyright 2007 shizuki
  * @license   http://www.gnu.org/licenses/gpl.txt  GNU GENERAL PUBLIC LICENSE Version 2, June 1991
  * @version   0.0.1
  * @link      http://shizuki.kinezumi.net/
  *
  * 0.0.1      Initial version.
  *
  **/

/**
  * When PDO can't be used, a wrapper class is read
  **/
if (!extension_loaded('PDO') || !extension_loaded('pdo_mysql')) {
	require_once('PDO/PDO.class.php');
}

class databaseMethod
{

/**
  * PDO object
  *
  * @var object
  **/
var $dbh;

/**
  * Caller plugin object
  *
  * @var object
  **/
var $plugin;

	// {{{ constructor
	/**
	  * Constructor
	  *
	  * @param string
	  *     MySQL host name
	  * @param string
	  *     MySQL databese name
	  * @param string
	  *     MySQL access user name
	  * @param string
	  *     MySQL access password
	  * @param object
	  *     Calling plugin
	  *
	  * @return void
	  **/
	function databaseMethod($host, $database, $user, $password, &$plugin)
	{
		$this->plugin =& $plugin;
		$dsn = 'mysql:host=' . $host . ';dbname=' . $database;
		$this->dbh = new PDO($dsn, $user, $password);
		if (!defined('_CHARSET') ) {
			global $DIR_LANG;
			$language = getLanguageName();
			include($DIR_LANG . ereg_replace( '[\\|/]', '', $language) . '.php');
		}
		if (_CHARSET == 'UTF-8') {
			$this->setCharset('utf8');
		}
	}

	// }}}
	// {{{ function setCharset($charset)

	/**
	  * Set client character set
	  *
	  * @param string
	  *     client character set
	  *
	  * @return void
	  **/
	function setCharset($charset)
	{
		$query = "SET NAMES " . $this->plugin->quoteSmart($charset);
		$result = $this->_exec($query);
	}

	// }}}
	// {{{ function _exec($query)

	/**
	  * Ececute SQL query
	  *
	  * @param string
	  *     SQL statement
	  *
	  * @return int
	  *     number of rows that were modified or deleted by the SQL statement
	  **/
	function _exec($query)
	{
		return $this->dbh->exec($query);
	}

	// }}}
	// {{{ function _exec($query)

	/**
	  * Ececute SQL query
	  *
	  * @param string
	  *     SQL statement
	  *
	  * @return object
	  *     PDOStatement object
	  **/
	function _query($query)
	{
		return $this->dbh->query($query);
	}

	// }}}
	// {{{ function buildSelectQuery($table, $parts)

	/**
	  * SQL statement builder ('SELECT' statment)
	  *
	  * @param string
	  *     SQL table name
	  * @param array
	  *     SQL statement parts
	  *     Format array('column' => COLUMN, 'where' => WHERE, 'order' => ORDER)
	  *     EXample : 'column' as column name
	  *                   all columns select   'column' => '*'
	  *                       as "SELECT * "
	  *                   single column select 'column' => 'column01'
	  *                       as "SELECT `column01` "
	  *                   multi columns select 'column' => array('column01', 'column02', ...)
	  *                       as "SELECT `column01`, `column02`, ... "
	  *                   column as Alias      'column' => array('column01' => 'alias01', ...)
	  *                       as "SELECT `column01` as 'alias01', ... "
	  *               'where' as search condition
	  *                   array('column01' => array('=', 'example'), ...)
	  *                       as "WHERE `column01` = 'example', ... "
	  *               'order' as sort order
	  *                   array('column01' => 'ASC', ...)
	  *                       as "ORDER BY `column01` ASC, ... "
	  *
	  * @return string
	  *     SQL statement
	  **/
	function buildSelectQuery($table, $parts)
	{
		extract($parts);
		if (is_array($column)) {
			if ($this->plugin->is_hash($column)) {
				$colData = array();
				foreach ($column as $col => $alias) {
					if ($alias == '') {
						$colData[] = '`' . $col . '`';
					} else {
						if (stripos($col, 'max(`') === 0 || stripos($col, 'min(`') === 0) {
							$colData[] = $col . ' as ' . $this->plugin->quoteSmart($alias);
						} else {
							$colData[] = '`' . $col . '` as ' . $this->plugin->quoteSmart($alias);
						}
					}
				}
				$column = implode(', ', $colData);
			} else {
				$column = '`' . implode('`, `', $column) . '`';
			}
		} elseif ($column != '*') {
			$column = '`' . $column . '`';
		}
		if (!empty($where)) {
			$qWhere = array();
			foreach ($where as $key => $value) {
				$qWhere[] = '`' . $key . '`' . $value[0] . $this->plugin->quoteSmart($value[1]);
			}
			$where = ' WHERE ' . implode(' AND ', $qWhere);
		}
		if (!empty($order)) {
			$qOrder = array();
			foreach ($order as $key => $value) {
				$qOrder[] = '`' . $key . '` ' . $value;
			}
			$order = ' ORDER BY ' . implode(', ', $qOrder);
		}
		$query  = 'SELECT ' . $column . ' FROM `' . $table . '`' . $where . $order;
		return $query;
	}

	// }}}
	// {{{ function getArray($table, $parts, $fetchMode = PDO::FETCH_ASSOC)

	/**
	  * get all records on the table by array
	  *
	  * @param string
	  *     @see databaseMethod::buildSelectQuery
	  * @param array
	  *     @see databaseMethod::buildSelectQuery
	  * @param int
	  *     @see http://www.php.net/manual/ja/ref.pdo.php
	  *
	  * @return array
	  *     PDOStatement object
	  **/
	function getArray($table, $parts, $fetchMode = PDO::FETCH_ASSOC)
	{
		$query = $this->_buildSelectQuery($table, $parts);
		return $this->_query($query);
	}

	// }}}
	// {{{ function getAllRowAssoc($table, $parts)

	/**
	  * get all records on the table by array
	  *
	  * @param string
	  *     @see databaseMethod::buildSelectQuery
	  * @param array
	  *     @see databaseMethod::buildSelectQuery
	  *
	  * @return array
	  *     containing all of the remaining rows in the result set by assoc
	  **/
	function getAllRowAssoc($table, $parts)
	{
		$query  = $this->buildSelectQuery($table, $parts);
		$result = $this->_query($query);
		if ($result) {
			return $result->fetchAll(PDO::FETCH_ASSOC);
		}
		return false;
	}

	// }}}
	// {{{ function getSingleRowAssoc($table, $parts)

	/**
	  * get single records on the table by array
	  *
	  * @param string
	  *     @see databaseMethod::buildSelectQuery
	  * @param array
	  *     @see databaseMethod::buildSelectQuery
	  *
	  * @return array
	  *     containing first of the remaining rows in the result set by assoc
	  **/
	function getSingleRowAssoc($table, $parts)
	{
		$query  = $this->buildSelectQuery($table, $parts);
		$result = $this->_query($query);
		if ($result) {
			return $result->fetch(PDO::FETCH_ASSOC);
		}
		return false;
	}

	// }}}
	// {{{ function getAllRowObject($table, $parts)

	/**
	  * get all records on the table by object
	  *
	  * @param string
	  *     @see databaseMethod::buildSelectQuery
	  * @param array
	  *     @see databaseMethod::buildSelectQuery
	  *
	  * @return object
	  *     containing all of the remaining rows in the result set by object
	  **/
	function getAllRowObject($table, $parts)
	{
		$query  = $this->buildSelectQuery($table, $parts);
		$result = $this->_query($query);
		if ($result) {
			return $result->fetchAll(PDO::FETCH_OBJ);
		}
		return false;
	}

	// }}}
	// {{{ function getSingleRowObject($table, $parts)

	/**
	  * get single records on the table by object
	  *
	  * @param string
	  *     @see databaseMethod::buildSelectQuery
	  * @param array
	  *     @see databaseMethod::buildSelectQuery
	  *
	  * @return object
	  *     containing first of the remaining rows in the result set by object
	  **/
	function getSingleRowObject($table, $parts)
	{
		$query  = $this->buildSelectQuery($table, $parts);
		$result = $this->_query($query);
		if ($result) {
			return $result->fetch(PDO::FETCH_OBJ);
		}
		return false;
	}

	// }}}
	// {{{ function getMinMaxColumnIntArray($table, $parts)

	/**
	  * The maximum of the designation column or the minimum value is acquired
	  *
	  * @param string
	  *     @see databaseMethod::buildSelectQuery
	  * @param array
	  *     @see databaseMethod::buildSelectQuery
	  *     and minimum or/and maximum value of column
	  *     EXample : 'maximum' as get maximum value of column
	  *                   'maximum' => 'column01'
	  *                       as "SELECT MAX(`column01`) as 'maximumcolumn01' "
	  *               'minimum' as get minimum value of column
	  *                   'minimum' => 'column01'
	  *                       as "SELECT MIN(`column01`) as 'minimumcolumn01' "
	  *
	  * @return array
	  *     containing first of the remaining rows in the result set by object
	  **/
	function getMinMaxColumnIntArray($table, $parts)
	{
		if (isset($parts['maximum'])) {
			if (!is_array($parts['column']) && isset($parts['column'])) {
				$col = $parts['column'];
				$parts['column'] = array($col=>'');
			}
			$max = '';
			if (is_array($parts['maximum'])) {
				foreach ($parts['maximum'] as $value) {
					$max = 'MAX(`' . $value . '`)';
					$parts['column'][$max] = 'maximum' . $value;
				}
			} else {
				$max = 'MAX(`' . $parts['maximum'] . '`)';
				$parts['column'][$max] = 'maximum' . $parts['maximum'];
			}
			unset($parts['maximum']);
		}
		if (isset($parts['minimum'])) {
			$min = '';
			if (is_array($parts['minimum'])) {
				foreach ($parts['minimum'] as $value) {
					$min = 'MIN(`' . $value . '`)';
					$parts['column'][$min] = 'minimum' . $value;
				}
			} else {
				$min = 'MIN(`' . $parts['minimum'] . '`)';
				$parts['column'][$min] = 'minimum' . $parts['minimum'];
			}
			unset($parts['minimum']);
		}
		return $this->getSingleRowAssoc($table, $parts);
	}

	// }}}
	// {{{ function resultOne($table, $parts, $column = 0)

	/**
	  * The price of the head of the designation column is acquired
	  *
	  * @param string
	  *     @see databaseMethod::buildSelectQuery
	  * @param array
	  *     @see databaseMethod::buildSelectQuery
	  * @param int
	  *     selece column No.
	  *
	  * @return array
	  *     
	  **/
	function resultOne($table, $parts, $column = 0)
	{
		if (!is_numeric($column)) {
			return false;
		}
		$query  = $this->buildSelectQuery($table, $parts);
		$result = $this->_query($query);
		if ($result) {
			return $result->fetchColumn();
		}
		return false;
	}

	// }}}
	// {{{ function getColumnAssoc($table, $parts)

	/**
	  * The price of everything's line of the designation column is acquired
	  *
	  * @param string
	  *     @see databaseMethod::buildSelectQuery
	  * @param array
	  *     @see databaseMethod::buildSelectQuery
	  *
	  * @return array
	  *     containing all of the remaining rows in the result set on selected column
	  **/
	function getColumnAssoc($table, $parts)
	{
		$query  = $this->buildSelectQuery($table, $parts);
		$result = $this->_query($query);
		$rowset = $result->fetchAll(PDO::FETCH_ASSOC);
		foreach ($rowset as $row) {
			foreach ($row as $column => $data) {
				$ret[$column][] = $data;
			}
		}
		return $ret;
	}

	// }}}
	// {{{ function insertInto($table, $columns, $values)

	/**
	  * Insert to new row set for Table
	  *
	  * @param string
	  *     MySQL database table name
	  * @param array
	  *     Example : array('column01', 'column02', 'column03', 'column04', ...)
	  *               as "(`column01`, `column02`, `column03`, `column04`, ...)"
	  * @param array
	  *     Example : array('text', 02, 'example', 04, ...)
	  *               as "VALUES ('text', 02, 'example', 04, ...)"
	  *
	  * @return string
	  *     representing the row ID of the last row that was inserted
	  **/
	function insertInto($table, $columns, $values)
	{
		$query = 'INSERT INTO `' . $table . '` (`'
			   . implode('`, `', $columns)
			   . '`) VALUES ('
			   . implode(', ', $this->plugin->quoteSmart($values))
			   . ')';
		$this->_exec($query);
		return $this->dbh->lastInsertId();
	}

	// }}}
	// {{{ function createNewTable($table, $columns, $index = '', $engine = '')

	/**
	  * Create new database Table
	  *
	  * @param string
	  *     MySQL database table name
	  * @param array
	  *     Example : array(
	  *                   'id' => array(
	  *                       'type' => 'integer', 'length' => 4, 'notnull' => true, 'autoincrement' => true,
	  *                   ),
	  *               as "`id` INT(11) NOTNULL AUTO_INCREMENT"
	  * @param array
	  *     Example : array(
	  *                   'prymary' => 'id',
	  *                   'index'   => array(
	  *                       'idkey'  => 'idkey',
	  *                   )
	  *               );
	  *               as "PRYMARY KEY `id`, KEY `idkey`('idkey')"
	  * @param string
	  *     MySQL database engine
	  *
	  * @return void
	  **/
	function createNewTable($table, $columns, $index = '', $engine = '')
	{
		$execColumns = array();
		$columnName  = array();
		foreach ($columns as $name => $declaration) {
			$strDeclaration = $this->getColumnDeclaration($declaration);
			$columnName[]   = $name;
			$execColumns[]  = ' `' . $name .'` ' . $strDeclaration;
		}
		$indexKeys = array();
		if (!empty($index) && is_array($index)) {
			foreach ($index as $key => $value) {
				switch ($key) {
					case 'prymary':
						if (in_array($value, $columnName) && $indexKeys['prymary'] == '') {
							$indexKeys['prymary'] = ', PRIMARY KEY (`' . $value . '`)';
						}
						break;
					case 'unique':
						$indexKeys['unique'] = $this->getIndexKeys($columnName, $value, 'UNIQUE KEY');
						break;
					case 'index':
						$indexKeys['index'] = $this->getIndexKeys($columnName, $value, 'KEY');
						break;
					case 'fulltext':
						$indexKeys['fulltext'] = $this->getIndexKeys($columnName, $value, 'FULLTEXT KEY');
						break;
					default :
						break;
				}
			}
		}
		$engine = $engine ? $engine : 'MyISAM';
		$query  = 'CREATE TABLE IF NOT EXISTS `' . $table . '` ('
				. implode(', ', $execColumns)
				. implode(' ', $indexKeys)
				. ')'
				. ' ENGINE=' . $engine;
		if (_CHARSET == 'UTF-8') {
			$query .= ' DEFAULT CHARACTER SET=utf8 COLLATE=utf8_unicode_ci';
		}
		return $this->_exec($query);
	}

	// }}}
	// {{{ function getColumnDeclaration($columnDec)

	/**
	  * Columns declaration settings
	  *
	  * @param array
	  *     Columns declaration
	  *         'type'   :    'char'
	  *                    or 'varchar'
	  *                    or 'text'
	  *                    or 'blob'
	  *                    or 'int'
	  *                    or 'integer'
	  *                    or 'boolean'
	  *                    or 'datetime'
	  *                    or 'timestamp'
	  *         'length' : bytes
	  *
	  * @return string
	  *     Columns declaration
	  **/
	function getColumnDeclaration($columnDec)
	{
		switch (strtolower($columnDec['type'])) {
			case 'char':
			case 'varchar':
				$length = 255;
				if (intval($columnDec['length']) > 0) {
					$length = intval($columnDec['length']);
				}
				$colType = strtoupper($columnDec['type']) . '(' . $length . ')';
				$typeDef = '';
				break;
			case 'text':
				if ($columnDec['length']) {
					$length = intval($columnDec['length']);
					if ($length <= 255) {
						$retData = 'TINYTEXT';
					} elseif ($length <= 65535) {
						$retData = 'TEXT';
					} elseif ($length <= 16777215) {
						$retData = 'MEDIUMTEXT';
					} else {
						$retData = 'LONGTEXT';
					}
				}
				$colType = 'TEXT';
				$typeDef = '';
				break;
			case 'blob':
				if ($columnDec['length']) {
					$length = intval($columnDec['length']);
					if ($length <= 255) {
						$retData = 'TINYBLOB';
					} elseif ($length <= 65535) {
						$retData = 'BLOB';
					} elseif ($length <= 16777215) {
						$retData = 'MEDIUMBLOB';
					} else {
						$retData = 'LONGBLOB';
					}
				}
				$colType = 'BLOB';
				$typeDef = '';
				break;
			case 'int':
			case 'integer':
				if ($columnDec['length']) {
					$length = intval($columnDec['length']);
					if ($length <= 1) {
						$retData = 'TINYINT';
					} elseif ($length == 2) {
						$retData = 'SMALLINT';
					} elseif ($length == 3) {
						$retData = 'MEDIUMINT';
					} elseif ($length == 4) {
						$retData = 'INT';
					} elseif ($length > 4) {
						$retData = 'BIGINT';
					}
				}
				$colType = 'INT';
				$typeDef = 0;
				break;
			case 'boolean':
				$colType = 'TINYINT(1)';
				$typeDef = 1;
				break;
			case 'datetime':
				$colType = 'DATETIME';
				$typeDef = 'CURRENT_TIMESTAMP';
				break;
			case 'timestamp':
				$colType = 'TIMESTAMP';
				$typeDef = 'CURRENT_TIMESTAMP';
				break;
			default:
				$colType = '';
				$typeDef = '';
		}
		return $colType . $this->getDeclarationOptions($columnDec, $typeDef);
	}

	// }}}
	// {{{ function getDeclarationOptions($columnDec, $typeDef)

	/**
	  * Columns declaration option settings
	  *
	  * @param array
	  *     Columns declaration
	  *         'notnull'       : boolean
	  *         'default'       : default vlue
	  *         'autoincrement' : boolean
	  * @param string
	  *     Default value of each type(auto)
	  *
	  * @return string
	  *     Columns declaration options
	  **/
	function getDeclarationOptions($columnDec, $typeDef)
	{
		$default = '';
		$notnull = '';
		if (array_key_exists('default', $columnDec)) {
			if ($columnDec['default'] === '') {
				if ($columnDec['notnull']) {
					if ($typeDef === '') {
						$default = " DEFAULT ''";
					} else {
						$default = ' DEFAULT ' . $this->plugin->quoteSmart($typeDef);
					}
					$notnull = ' NOT NULL';
				} else {
					$default = ' DEFAULT NULL';
				}
			} else {
				$default = ' DEFAULT ' . $this->plugin->quoteSmart($columnDec['default']);
			}
		} elseif ($columnDec['notnull']) {
			$notnull = ' NOT NULL';
		}
		if ($columnDec['autoincrement']) {
			$default = ' AUTO_INCREMENT';
		}
		return $notnull . $default;
	}

	// }}}
	// {{{ function getIndexKeys($columns, $keys, $type = 'UNIQUE KEY')

	/**
	  * Index key string for SQL statement
	  *
	  * @param array
	  *     Columns
	  *     @see databaseMethod::createNewTable()
	  * @param array
	  *     Columns of index keys
	  *     @see databaseMethod::createNewTable()
	  * @param string
	  *        'UNIQUE KEY'
	  *     or 'KEY'
	  *     or 'FULLTEXT KEY'
	  *
	  * @return string
	  *     Index key string for SQL statement
	  **/
	function getIndexKeys($columns, $keys, $type = 'UNIQUE KEY')
	{
		$sql = array();
		if (!is_array($keys)) {
			return '';
		}
		foreach ($keys as $key => $value) {
			if (in_array($value, $columns)) {
				$sql[] = $type . ' `' . $key . '` (`' . $value . '`)';
			}
		}
		if (!empty($sql[0])) {
			return ', ' . implode(', ', $sql);
		} else {
			return '';
		}
	}

	// }}}
	// {{{ function dropTable($table)

	/**
	  * Drop table from database
	  *
	  * @param string
	  *     MySQL database table name
	  *
	  * @return int
	  *     number of rows that were modified or deleted
	  **/
	function dropTable($table)
	{
		$query = 'DROP TABLE IF EXISTS `' . $table . '`';
		return $this->_exec($query);
	}

	// }}}
	// {{{ function _prepare($query)

	/**
	  * PDO->prepare alias
	  *
	  * @param string
	  *     SQL statement
	  *
	  * @return object
	  *     PDOStatement object
	  **/
	function _prepare($query)
	{
		return $this->dbh->prepare($query);
	}

	// }}}



















}



